package wzj.mybatisplusdemo.tools;

import org.springframework.ai.tool.annotation.Tool;
import org.springframework.stereotype.Component;
import wzj.mybatisplusdemo.po.Creation;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class CreationSqlTool {

    private final DatabaseOperationTool databaseOperationTool;
    private static final String TABLE_NAME = "creations";
    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    public CreationSqlTool(DatabaseOperationTool databaseOperationTool) {
        this.databaseOperationTool = databaseOperationTool;
    }

    /**
     * 获取creation表的详细结构信息，帮助AI生成正确SQL
     */
    @Tool(description = "获取creations表的结构信息，包括字段名、数据类型等")
    public List<Map<String, Object>> getCreationTableStructure() {
        return databaseOperationTool.getTableStructure(TABLE_NAME);
    }

    /**
     * 执行针对creation表的查询SQL
     */
    @Tool(description = "执行针对creations表的查询SQL语句，参数为完整的SQL字符串")
    public List<Map<String, Object>> executeCreationQuery(String sql) {
        if (!isValidCreationSql(sql)) {
            throw new RuntimeException("SQL只能操作creations表: " + sql);
        }
        return databaseOperationTool.executeQuery(sql);
    }

    /**
     * 执行针对creation表的更新SQL（INSERT/UPDATE/DELETE）
     */
    @Tool(description = "执行针对creations表的更新SQL语句（INSERT/UPDATE/DELETE），参数为完整的SQL字符串")
    public int executeCreationUpdate(String sql) {
        if (!isValidCreationSql(sql)) {
            throw new RuntimeException("SQL只能操作creations表: " + sql);
        }
        logSqlOperation(sql);
        return databaseOperationTool.executeUpdate(sql);
    }

    /**
     * 生成创建文章的SQL示例
     */
    @Tool(description = "生成插入新文章的SQL示例，帮助理解格式")
    public String getCreateExampleSql(Creation creation) {
        return String.format(
                "INSERT INTO %s (s_title, s_content, s_img, s_click, s_userid, s_cgy, s_keyword, s_createtime) " +
                        "VALUES ('%s', '%s', '%s', %d, %d, %d, '%s', '%s')",
                TABLE_NAME,
                escapeSql(creation.getTitle()),
                escapeSql(creation.getContent()),
                escapeSql(creation.getImg()),
                creation.getClick(),
                creation.getUserId(),
                creation.getCgyId(),
                escapeSql(creation.getKeyword()),
                creation.getCreateTime() != null ? DATE_FORMAT.format(creation.getCreateTime()) : DATE_FORMAT.format(new Date())
        );
    }

    /**
     * 生成更新文章的SQL示例
     */
    @Tool(description = "生成更新文章的SQL示例，参数为文章ID和要更新的字段键值对")
    public String getUpdateExampleSql(Integer id, Map<String, Object> fields) {
        // 方法实现保持不变
        StringBuilder setClause = new StringBuilder();
        for (Map.Entry<String, Object> entry : fields.entrySet()) {
            String column = mapFieldToColumn(entry.getKey());
            if (column == null) continue;

            if (setClause.length() > 0) {
                setClause.append(", ");
            }

            if (entry.getValue() instanceof String) {
                setClause.append(column).append(" = '").append(escapeSql((String) entry.getValue())).append("'");
            } else {
                setClause.append(column).append(" = ").append(entry.getValue());
            }
        }

        return String.format("UPDATE %s SET %s WHERE s_id = %d", TABLE_NAME, setClause, id);
    }

    // 其他辅助方法保持不变
    private boolean isValidCreationSql(String sql) {
        String lowerSql = sql.toLowerCase();
        return lowerSql.contains("creations") &&
                (lowerSql.startsWith("select") ||
                        lowerSql.startsWith("insert") ||
                        lowerSql.startsWith("update") ||
                        lowerSql.startsWith("delete")) &&
                !lowerSql.contains(";") &&
                !lowerSql.contains("union");
    }

    private String escapeSql(String value) {
        if (value == null) return "";
        return value.replace("'", "''");
    }

    private String mapFieldToColumn(String fieldName) {
        switch (fieldName) {
            case "id": return "s_id";
            case "title": return "s_title";
            case "content": return "s_content";
            case "img": return "s_img";
            case "click": return "s_click";
            case "userId": return "s_userid";
            case "cgyId": return "s_cgy";
            case "keyword": return "s_keyword";
            case "createTime": return "s_createtime";
            default: return null;
        }
    }

    private void logSqlOperation(String sql) {
        System.out.println("执行SQL: " + sql + " 时间: " + new Date());
    }
}

